package org.azmat.demojpa.SQL.JPA.Dao;

import org.azmat.demojpa.SQL.JPA.Entity.Course;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

public interface CourseDao extends JpaRepository<Course,Integer> {
    // 使用JpaRepository的约定命名规范查询，DAO层无须手动编写SQL或JPQL语句
    // 查询学分为xx的选修(或必修)课，按照命名规范，无须手动编写SQL语句
    // 相当于SQL : SELECT * FROM Course WHERE coursescore = xx and courseType = '必修'

    public List<Course> findByCourseScoreAndCourseType(int courseScore,String courseType);

    // 查询学分为xx的选修(或必修)课，按照命名规范，无须手动编写SQL语句
    // 相当于SQL : SELECT * FROM Course WHERE coursescore = xx or courseType = '选修'
    public List<Course> findByCourseScoreOrCourseType(int courseScore,String courseType);

    // 查询课时小于xxx到xxx的课程
    public List<Course> findByCourseTimeBetween(int courseHour1,int courseHour2);

    // 查询课程课时小于xxx的课程
    public List<Course> findByCourseTimeLessThan(int courseHour);

    // 查询课程包含xxx的课程
    public List<Course> findByCourseNameContaining(String courseName);

    // 自定义查询，DAO层需手动编写SQL或JPQL语句
    // 使用JPQL，默认
    @Query("SELECT c FROM course c WHERE c.courseScore > ?1")
    public List<Course> selectScore1(int courseScore);

    // 使用JPQL，默认，多个参数
    @Query("SELECT c FROM course c WHERE c.courseScore > ?1 and c.courseTime = ?2")
    public List<Course> selectScore2(int courseScore,int courseTime);

    // 使用SQL
    @Query(value = "select * from course  c where c.courseScore > ?1",nativeQuery=true)
    public List<Course> selectScore3(int courseScore);

    // 使用命名参数
    @Query("SELECT c FROM course c WHERE c.courseScore > :courseScore")
    public List<Course> selectScore4(@Param("courseScore") int courseScore);

    // 模糊查询
    @Query("SELECT c FROM course c WHERE c.courseName like %?1%")
    public List<Course> selectName1(String courseName);

    // 自定义的增，删，改，查还要加上@Transactional注解和@Modifying注解,否则会报错
    @Transactional
    @Modifying
    @Query("update course set courseName = ?1 , courseScore = ?2 ,courseTime = ?3 ,courseType = ?4 where courseId = ?5")
    public void updateCourse(String courseName,int courseScore,int courseTime,String courseType,int courseId);



}
